package cate.common.game.trace;

import easy.java.io.file.FileWrite;
import easy.mysql.map.KeyType;
import easy.mysql.map.anno.MyColumn;
import easy.mysql.map.anno.MyTable;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;

import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.sql.Timestamp;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.ZonedDateTime;
import java.util.Arrays;
import java.util.Date;
import java.util.List;

@Slf4j
public class SQLiteHelper {

    public static final List<String> KEYWORDS = Arrays.asList(
            "ABORT",
            "ACTION",
            "ADD",
            "AFTER",
            "ALL",
            "ALTER",
            "ALWAYS",
            "ANALYZE",
            "AND",
            "AS",
            "ASC",
            "ATTACH",
            "AUTOINCREMENT",
            "BEFORE",
            "BEGIN",
            "BETWEEN",
            "BY",
            "CASCADE",
            "CASE",
            "CAST",
            "CHECK",
            "COLLATE",
            "COLUMN",
            "COMMIT",
            "CONFLICT",
            "CONSTRAINT",
            "CREATE",
            "CROSS",
            "CURRENT",
            "CURRENT_DATE",
            "CURRENT_TIME",
            "CURRENT_TIMESTAMP",
            "DATABASE",
            "DEFAULT",
            "DEFERRABLE",
            "DEFERRED",
            "DELETE",
            "DESC",
            "DETACH",
            "DISTINCT",
            "DO",
            "DROP",
            "EACH",
            "ELSE",
            "END",
            "ESCAPE",
            "EXCEPT",
            "EXCLUDE",
            "EXCLUSIVE",
            "EXISTS",
            "EXPLAIN",
            "FAIL",
            "FILTER",
            "FIRST",
            "FOLLOWING",
            "FOR",
            "FOREIGN",
            "FROM",
            "FULL",
            "GENERATED",
            "GLOB",
            "GROUP",
            "GROUPS",
            "HAVING",
            "IF",
            "IGNORE",
            "IMMEDIATE",
            "IN",
            "INDEX",
            "INDEXED",
            "INITIALLY",
            "INNER",
            "INSERT",
            "INSTEAD",
            "INTERSECT",
            "INTO",
            "IS",
            "ISNULL",
            "JOIN",
            "KEY",
            "LAST",
            "LEFT",
            "LIKE",
            "LIMIT",
            "MATCH",
            "MATERIALIZED",
            "NATURAL",
            "NO",
            "NOT",
            "NOTHING",
            "NOTNULL",
            "NULL",
            "NULLS",
            "OF",
            "OFFSET",
            "ON",
            "OR",
            "ORDER",
            "OTHERS",
            "OUTER",
            "OVER",
            "PARTITION",
            "PLAN",
            "PRAGMA",
            "PRECEDING",
            "PRIMARY",
            "QUERY",
            "RAISE",
            "RANGE",
            "RECURSIVE",
            "REFERENCES",
            "REGEXP",
            "REINDEX",
            "RELEASE",
            "RENAME",
            "REPLACE",
            "RESTRICT",
            "RETURNING",
            "RIGHT",
            "ROLLBACK",
            "ROW",
            "ROWS",
            "SAVEPOINT",
            "SELECT",
            "SET",
            "TABLE",
            "TEMP",
            "TEMPORARY",
            "THEN",
            "TIES",
            "TO",
            "TRANSACTION",
            "TRIGGER",
            "UNBOUNDED",
            "UNION",
            "UNIQUE",
            "UPDATE",
            "USING",
            "VACUUM",
            "VALUES",
            "VIEW",
            "VIRTUAL",
            "WHEN",
            "WHERE",
            "WINDOW",
            "WITH",
            "WITHOUT"
    );

    /**
     * 生成对应类的SQL文件
     */
    public static void generateSQLiteSQL(Class<?> clazz) {
        MyTable myTable = clazz.getAnnotation(MyTable.class);
        if (myTable == null || StringUtils.isBlank(myTable.table())) {
            log.error("找不到表明名");
            return;
        }
        String tableName = myTable.table();
        StringBuilder sb = new StringBuilder();
        sb.append("CREATE TABLE IF NOT EXISTS ").append(tableName).append(" (");
        boolean mark = false;
        for (Field field : clazz.getDeclaredFields()) {
            MyColumn myColumn = field.getAnnotation(MyColumn.class);
            if (myColumn == null) {
                continue;
            }
            String sqlType = null;
            if (feq(field, String.class)) {
                sqlType = "TEXT";
                SQLiteTextLength textLength = field.getAnnotation(SQLiteTextLength.class);
                if (textLength != null) {
                    sqlType += "(" + textLength.length() + ")";
                }
            } else if (feq(field, int.class)) {
                sqlType = "INTEGER";
            } else if (feq(field, Integer.class)) {
                sqlType = "INTEGER";
            } else if (feq(field, long.class)) {
                sqlType = "INTEGER";
            } else if (feq(field, Long.class)) {
                sqlType = "INTEGER";
            } else if (feq(field, double.class)) {
                sqlType = "REAL";
            } else if (feq(field, Double.class)) {
                sqlType = "REAL";
            } else if (feq(field, boolean.class)) {
                sqlType = "NUMERIC";
            } else if (feq(field, Boolean.class)) {
                sqlType = "NUMERIC";
            } else if (feq(field, byte.class)) {
                sqlType = "INTEGER";
            } else if (feq(field, Byte.class)) {
                sqlType = "INTEGER";
            } else if (feq(field, short.class)) {
                sqlType = "INTEGER";
            } else if (feq(field, Short.class)) {
                sqlType = "INTEGER";
            } else if (feq(field, float.class)) {
                sqlType = "REAL";
            } else if (feq(field, Float.class)) {
                sqlType = "REAL";
            } else if (feq(field, Date.class)) {
                sqlType = "INTEGER";
            } else if (feq(field, Timestamp.class)) {
                sqlType = "INTEGER";
            } else if (feq(field, ZonedDateTime.class)) {
                sqlType = "INTEGER";
            } else if (feq(field, LocalDateTime.class)) {
                sqlType = "INTEGER";
            } else if (feq(field, LocalDate.class)) {
                sqlType = "INTEGER";
            } else if (feq(field, BigDecimal.class)) {
                sqlType = "NUMERIC";
            } else if (feq(field, char.class)) {
                sqlType = "TEXT";
            } else if (feq(field, Character.class)) {
                sqlType = "TEXT";
            }
            if (sqlType == null) {
                continue;
            }
            if (mark) {
                sb.append(",");
            } else {
                mark = true;
            }
            sb.append("\n");
            sb.append("\t").append(getSafeName(field.getName())).append(" ").append(sqlType);
            if (myColumn.keyType().equals(KeyType.PRIMARY)) {
                sb.append(" PRIMARY KEY NOT NULL");
            }
        }
        sb.append("\n");
        sb.append(");");

        try {
            String fileStr = sb.toString();
            FileWrite.i.writeString(".tmp/" + tableName + ".sql", fileStr);
        } catch (Exception e) {
            log.error("", e);
        }
    }

    /**
     * 获取安全的列名称
     */
    private static String getSafeName(String name) {
        if (KEYWORDS.contains(name.toUpperCase())) {
            return "\"" + name + "\"";
        }
        return name;
    }

    /**
     * 类是否相同
     */
    private static boolean feq(Field field, Class<?> clz) {
        return field.getType().equals(clz);
    }
}
